 /// <summary>  
/// 类说明：公共的数据库访问访问类  
/// 编码日期：2010-4-22  
/// 编 码 人：苏飞  
/// 联系方式：361983679  Email：sufei.1013@163.com  Blogs:http://www.sufeinet.com  
/// 修改日期：2013-08-15  
/// </summary>  
using System;  
using System.Collections.Generic;  
using System.Text;  
using System.Data;  
using System.Data.SqlClient;  
using System.Collections;  
 
namespace MSSqlHelper  
{  
    /// <summary>  
    /// 数据库的通用访问代码 苏飞修改  
    /// 此类为抽象类，  
    /// 不允许实例化，在应用时直接调用即可  
    /// </summary>  
    public abstract class SqlHelper
    {
        /// <summary>  
        /// 数据库连接字符串  
        /// </summary>  
        public static readonly string connectionString =
            "Data Source=10.13.10.96; Initial Catalog=SIEDB; User Id=sa; Password=swumcf&653#78*SLK2123;Connect Timeout=5000";
  
        // Hashtable to store cached parameters  
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());  
 
        #region //ExecteNonQuery方法  
        /// <summary>  
        ///执行一个不需要返回值的SqlCommand命令，通过指定专用的连接字符串。  
        /// 使用参数数组形式提供参数列表   
        /// </summary>  
        /// <param name="connectionString">一个有效的数据库连接字符串</param>  
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>  
        public static int ExecteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)  
        {  
            SqlCommand cmd = new SqlCommand();  
            using (SqlConnection conn = new SqlConnection(connectionString))  
            {  
                //通过PrePareCommand方法将参数逐个加入到SqlCommand的参数集合中  
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);  
                int val = cmd.ExecuteNonQuery();  
                //清空SqlCommand中的参数列表  
                cmd.Parameters.Clear();  
                return val;  
            }  
        }  
  
        /// <summary>  
        ///执行一个不需要返回值的SqlCommand命令，通过指定专用的连接字符串。  
        /// 使用参数数组形式提供参数列表   
        /// </summary>  
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>  
        public static int ExecteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)  
        {  
            return ExecteNonQuery(connectionString, cmdType, cmdText, commandParameters);  
        }  
  
        /// <summary>  
        ///存储过程专用  
        /// </summary>  
        /// <param name="cmdText">存储过程的名字</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>  
        public static int ExecteNonQueryProducts(string cmdText, params SqlParameter[] commandParameters)  
        {  
            return ExecteNonQuery(CommandType.StoredProcedure, cmdText, commandParameters);  
        }  
  
        /// <summary>  
        ///Sql语句专用  
        /// </summary>  
        /// <param name="cmdText">T_Sql语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>  
        public static int ExecteNonQueryText(string cmdText, params SqlParameter[] commandParameters)  
        {  
            return ExecteNonQuery(CommandType.Text, cmdText, commandParameters);  
        }  
 
        #endregion  
 
        #region//GetTable方法  
  
        /// <summary>  
        /// 执行一条返回结果集的SqlCommand，通过一个已经存在的数据库连接  
        /// 使用参数数组提供参数  
        /// </summary>  
        /// <param name="connecttionString">一个现有的数据库连接</param>  
        /// <param name="cmdTye">SqlCommand命令类型</param>  
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>  
        public static DataTableCollection GetTable(string connecttionString, CommandType cmdTye, string cmdText, SqlParameter[] commandParameters)  
        {  
            SqlCommand cmd = new SqlCommand();  
            DataSet ds = new DataSet();  
            using (SqlConnection conn = new SqlConnection(connecttionString))  
            {  
                PrepareCommand(cmd, conn, null, cmdTye, cmdText, commandParameters);  
                SqlDataAdapter adapter = new SqlDataAdapter();  
                adapter.SelectCommand = cmd;  
                adapter.Fill(ds);  
            }  
            DataTableCollection table = ds.Tables;  
            return table;  
        }  
  
        /// <summary>  
        /// 执行一条返回结果集的SqlCommand，通过一个已经存在的数据库连接  
        /// 使用参数数组提供参数  
        /// </summary>  
        /// <param name="cmdTye">SqlCommand命令类型</param>  
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>  
        public static DataTableCollection GetTable(CommandType cmdTye, string cmdText, SqlParameter[] commandParameters)  
        {  
            return GetTable(SqlHelper.connectionString, cmdTye, cmdText, commandParameters);  
        }  
  
        /// <summary>  
        /// 存储过程专用  
        /// </summary>  
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>  
        public static DataTableCollection GetTableProducts(string cmdText, SqlParameter[] commandParameters)  
        {  
            return GetTable(CommandType.StoredProcedure, cmdText, commandParameters);  
        }  
  
        /// <summary>  
        /// Sql语句专用  
        /// </summary>  
        /// <param name="cmdText"> T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns>  
        public static DataTableCollection GetTableText(string cmdText, SqlParameter[] commandParameters)  
        {  
            return GetTable(CommandType.Text, cmdText, commandParameters);  
        }  
 
        #endregion  
  
        /// <summary>  
        /// 为执行命令准备参数  
        /// </summary>  
        /// <param name="cmd">SqlCommand 命令</param>  
        /// <param name="conn">已经存在的数据库连接</param>  
        /// <param name="trans">数据库事物处理</param>  
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
        /// <param name="cmdText">Command text，T-SQL语句 例如 Select * from Products</param>  
        /// <param name="cmdParms">返回带参数的命令</param>  
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)  
        {  
            //判断数据库连接状态  
            if (conn.State != ConnectionState.Open)  
                conn.Open();  
            cmd.Connection = conn;  
            cmd.CommandText = cmdText;  
            //判断是否需要事物处理  
            if (trans != null)  
                cmd.Transaction = trans;  
            cmd.CommandType = cmdType;  
            if (cmdParms != null)  
            {  
                foreach (SqlParameter parm in cmdParms)  
                    cmd.Parameters.Add(parm);  
            }  
        }  
  
        /// <summary>  
        /// Execute a SqlCommand that returns a resultset against the database specified in the connection string   
        /// using the provided parameters.  
        /// </summary>  
        /// <param name="connectionString">一个有效的数据库连接字符串</param>  
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>A SqlDataReader containing the results</returns>  
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)  
        {  
            SqlCommand cmd = new SqlCommand();  
            SqlConnection conn = new SqlConnection(connectionString);  
            // we use a try/catch here because if the method throws an exception we want to   
            // close the connection throw code, because no datareader will exist, hence the   
            // commandBehaviour.CloseConnection will not work  
            try  
            {  
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);  
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
                cmd.Parameters.Clear();  
                return rdr;  
            }  
            catch  
            {  
                conn.Close();  
                throw;  
            }  
        }  
 
        #region//ExecuteDataSet方法  
  
        /// <summary>  
        /// return a dataset  
        /// </summary>  
        /// <param name="connectionString">一个有效的数据库连接字符串</param>  
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>return a dataset</returns>  
        public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)  
        {  
            SqlCommand cmd = new SqlCommand();  
            try  
            {  
                using (SqlConnection conn = new SqlConnection(connectionString))  
                {  
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);  
                    SqlDataAdapter da = new SqlDataAdapter();  
                    DataSet ds = new DataSet();  
                    da.SelectCommand = cmd;  
                    da.Fill(ds);  
                    return ds;  
                }  
            }  
            catch  
            {  
                throw;  
            }  
        }  
  
        /// <summary>  
        /// 返回一个DataSet  
        /// </summary>  
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>return a dataset</returns>  
        public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)  
        {  
            return ExecuteDataSet(connectionString, cmdType, cmdText, commandParameters);  
        }  
  
        /// <summary>  
        /// 返回一个DataSet  
        /// </summary>  
        /// <param name="cmdText">存储过程的名字</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>return a dataset</returns>  
        public static DataSet ExecuteDataSetProducts(string cmdText, params SqlParameter[] commandParameters)  
        {  
            return ExecuteDataSet(connectionString, CommandType.StoredProcedure, cmdText, commandParameters);  
        }  
  
        /// <summary>  
        /// 返回一个DataSet  
        /// </summary>  
        /// <param name="cmdText">T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>return a dataset</returns>  
        public static DataSet ExecuteDataSetText(string cmdText, params SqlParameter[] commandParameters)  
        {  
            return ExecuteDataSet(connectionString, CommandType.Text, cmdText, commandParameters);  
        }  
  
        public static DataView ExecuteDataSet(string connectionString, string sortExpression, string direction, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)  
        {  
            SqlCommand cmd = new SqlCommand();  
            try  
            {  
                using (SqlConnection conn = new SqlConnection(connectionString))  
                {  
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);  
                    SqlDataAdapter da = new SqlDataAdapter();  
                    DataSet ds = new DataSet();  
                    da.SelectCommand = cmd;  
                    da.Fill(ds);  
                    DataView dv = ds.Tables[0].DefaultView;  
                    dv.Sort = sortExpression + " " + direction;  
                    return dv;  
                }  
            }  
            catch  
            {  
  
                throw;  
            }  
        }  
        #endregion  
 
        #region // ExecuteScalar方法  
  
        /// <summary>  
        /// 返回第一行的第一列  
        /// </summary>  
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>返回一个对象</returns>  
        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)  
        {  
            return ExecuteScalar(SqlHelper.connectionString, cmdType, cmdText, commandParameters);  
        }  
  
        /// <summary>  
        /// 返回第一行的第一列存储过程专用  
        /// </summary>  
        /// <param name="cmdText">存储过程的名字</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>返回一个对象</returns>  
        public static object ExecuteScalarProducts(string cmdText, params SqlParameter[] commandParameters)  
        {  
            return ExecuteScalar(SqlHelper.connectionString, CommandType.StoredProcedure, cmdText, commandParameters);  
        }  
  
        /// <summary>  
        /// 返回第一行的第一列Sql语句专用  
        /// </summary>  
        /// <param name="cmdText">者 T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>返回一个对象</returns>  
        public static object ExecuteScalarText(string cmdText, params SqlParameter[] commandParameters)  
        {  
            return ExecuteScalar(SqlHelper.connectionString, CommandType.Text, cmdText, commandParameters);  
        }  
  
        /// <summary>  
        /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string   
        /// using the provided parameters.  
        /// </summary>  
        /// <remarks>  
        /// e.g.:    
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));  
        /// </remarks>  
        /// <param name="connectionString">一个有效的数据库连接字符串</param>  
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>  
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)  
        {  
            SqlCommand cmd = new SqlCommand();  
  
            using (SqlConnection connection = new SqlConnection(connectionString))  
            {  
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);  
                object val = cmd.ExecuteScalar();  
                cmd.Parameters.Clear();  
                return val;  
            }  
        }  
  
        /// <summary>  
        /// Execute a SqlCommand that returns the first column of the first record against an existing database connection   
        /// using the provided parameters.  
        /// </summary>  
        /// <remarks>  
        /// e.g.:    
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));  
        /// </remarks>  
        /// <param name="connectionString">一个有效的数据库连接字符串</param>  
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>  
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>  
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>  
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>  
        public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)  
        {  
            SqlCommand cmd = new SqlCommand();  
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);  
            object val = cmd.ExecuteScalar();  
            cmd.Parameters.Clear();  
            return val;  
        }  
 
        #endregion  
  
        /// <summary>  
        /// add parameter array to the cache  
        /// </summary>  
        /// <param name="cacheKey">Key to the parameter cache</param>  
        /// <param name="cmdParms">an array of SqlParamters to be cached</param>  
        public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)  
        {  
            parmCache[cacheKey] = commandParameters;  
        }  
  
        /// <summary>  
        /// Retrieve cached parameters  
        /// </summary>  
        /// <param name="cacheKey">key used to lookup parameters</param>  
        /// <returns>Cached SqlParamters array</returns>  
        public static SqlParameter[] GetCachedParameters(string cacheKey)  
        {  
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];  
            if (cachedParms == null)  
                return null;  
            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];  
            for (int i = 0, j = cachedParms.Length; i < j; i++)  
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();  
            return clonedParms;  
        }  
  
        /// <summary>  
        /// 检查是否存在  
        /// </summary>  
        /// <param name="strSql">Sql语句</param>  
        /// <param name="cmdParms">参数</param>  
        /// <returns>bool结果</returns>  
        public static bool Exists(string strSql, params SqlParameter[] cmdParms)  
        {  
            int cmdresult = Convert.ToInt32(ExecuteScalar(connectionString, CommandType.Text, strSql, cmdParms));  
            if (cmdresult == 0)  
            {  
                return false;  
            }  
            else  
            {  
                return true;  
            }  
        }

        #region 常用函数

        public static string GetTableColoumnDescription(string TableName,string ColoumnName)
        {
            string sqltext=@"select
                            [列说明]=isnull(e.[value],'')
                        from
                            sys.columns a
                        left join
                            sys.types b on a.user_type_id=b.user_type_id
                        inner join
                            sys.objects c on a.object_id=c.object_id and c.Type='U'
                        left join
                            syscomments d on a.default_object_id=d.ID
                        left join
                            sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1
                        left join
                            sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1
                        		where c.Name =@TableName and a.Name=@ColoumnName";
            SqlParameter[] parameters = { new SqlParameter("@TableName", TableName), new SqlParameter("@ColoumnName", ColoumnName) };
            DataTable dt= MSSqlHelper.SqlHelper.ExecuteDataSet(CommandType.Text,sqltext,parameters).Tables[0];
            return dt.Rows[0][0].ToString().Trim();
        }


        #endregion
    }  
}  